# src/db/artwork_db.py
from src.db.connection import get_db_connection, close_db_connection
from src.utils.logger import get_logger

logger = get_logger(__name__)

def create_artwork(
    title: str,
    description: str,
    price: float,
    creator: str,
    owner_id: int,
    category: str = "",  # 新增：艺术品类别（如绘画、雕塑）
    era: str = ""        # 新增：创作年代（如当代、现代）
) -> int:
    """创建新艺术品（返回艺术品ID，失败返回None）"""
    conn = get_db_connection()
    if not conn:
        return None
    
    try:
        with conn.cursor() as cursor:
            sql = """
            INSERT INTO artwork 
            (title, description, price, creator, owner_id, category, era, create_time)
            VALUES (%s, %s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
            """
            cursor.execute(
                sql,
                (title, description, price, creator, owner_id, category, era)
            )
            conn.commit()
            artwork_id = cursor.lastrowid
            logger.info(f"艺术品创建成功（artwork_id: {artwork_id}）")
            return artwork_id
    except Exception as e:
        conn.rollback()
        logger.error(f"创建艺术品失败（title: {title}）：{str(e)}")
        return None
    finally:
        close_db_connection(conn)

def get_all_artworks(
    category: str = None,  # 按类别筛选（可选）
    era: str = None,       # 按年代筛选（可选）
    keyword: str = None    # 按关键词搜索（标题/创作者，可选）
) -> list:
    """获取艺术品列表（支持筛选，返回字典列表）"""
    conn = get_db_connection()
    if not conn:
        return []
    
    try:
        with conn.cursor() as cursor:
            # 基础SQL
            sql = "SELECT * FROM artwork ORDER BY create_time DESC"
            params = []
            
            # 拼接筛选条件
            if category or era or keyword:
                where_clauses = []
                if category:
                    where_clauses.append("category = %s")
                    params.append(category)
                if era:
                    where_clauses.append("era = %s")
                    params.append(era)
                if keyword:
                    where_clauses.append("(title LIKE %s OR creator LIKE %s)")
                    params.extend([f"%{keyword}%", f"%{keyword}%"])
                
                sql = f"SELECT * FROM artwork WHERE {' AND '.join(where_clauses)} ORDER BY create_time DESC"
            
            cursor.execute(sql, params)
            artworks = cursor.fetchall()
            logger.info(f"获取艺术品列表成功（数量：{len(artworks)}）")
            return artworks
    except Exception as e:
        logger.error(f"获取艺术品列表失败：{str(e)}")
        return []
    finally:
        close_db_connection(conn)

def get_artwork_by_id(artwork_id: int) -> dict:
    """通过ID查询艺术品详情"""
    conn = get_db_connection()
    if not conn:
        return None
    
    try:
        with conn.cursor() as cursor:
            sql = "SELECT * FROM artwork WHERE artwork_id = %s"
            cursor.execute(sql, (artwork_id,))
            artwork = cursor.fetchone()
            if artwork:
                logger.info(f"获取艺术品详情成功（artwork_id: {artwork_id}）")
            else:
                logger.warning(f"艺术品不存在（artwork_id: {artwork_id}）")
            return artwork
    except Exception as e:
        logger.error(f"获取艺术品详情失败（artwork_id: {artwork_id}）：{str(e)}")
        return None
    finally:
        close_db_connection(conn)